﻿using System;
using System.Data;
using System.Data.SqlClient;
using Entities;
using System.Collections.Generic;

namespace DataAccess
{
        public class TicketDAL : BaseObject
        {
            public TicketDAL() : base()
            {

            }
            #region Public Methods
            ///<summary>
            ///Create Obj from ireader.
            ///</summary>

            private Ticket GetObjectFromReader(IDataReader reader)
            {
                Ticket temp = new Ticket();
                temp.TicketID = (reader["TicketID"] is DBNull) ? int.MinValue : (System.Int32)reader["TicketID"];
                temp.OrderID = (reader["OrderID"] is DBNull) ? int.MinValue : (System.Int32)reader["OrderID"];
                temp.FromStation = (reader["FromStation"] is DBNull) ? int.MinValue : (System.Int32)reader["FromStation"];
                temp.ToStation = (reader["ToStation"] is DBNull) ? int.MinValue : (System.Int32)reader["ToStation"];
                temp.FromStationNumber = (reader["FromStationNumber"] is DBNull) ? int.MinValue : (System.Int32)reader["FromStation"];
                temp.ToStationNumber = (reader["ToStationNumber"] is DBNull) ? int.MinValue : (System.Int32)reader["ToStation"];
                temp.ScheduleID = (reader["ScheduleID"] is DBNull) ? int.MinValue : (System.Int32)reader["ScheduleID"];
                temp.SeatNo = (reader["SeatNo"] is DBNull) ? int.MinValue : (System.Int32)reader["SeatNo"];
                temp.CoachTypeID = (reader["CoachTypeID"] is DBNull) ? int.MinValue : (System.Int32)reader["CoachTypeID"];
                //temp.Fare = (reader["Fare"] is DBNull) ? float.MinValue : (System.Int32)reader["Fare"];
                temp.Status = (reader["Status"] is DBNull) ? int.MinValue : (System.Int32)reader["Status"];
                temp.Date = (reader["Date"] is DBNull) ? DateTime.MinValue : (System.DateTime)reader["Date"];
                temp.ExpirationDate = (reader["ExpirationDate"] is DBNull) ? DateTime.MinValue : (System.DateTime)reader["ExpirationDate"];
                return temp;
            }

            ///<summary>
            ///Get List Ticket and store in a list 
            ///</summary>
            public Ticket Get_Ticket_By_ID(int TicketID)
            {
                using (SqlConnection conn = CreateConnection())
                {
                    SqlCommand cmd = new SqlCommand(usp_SelecttbTicketsByID, conn);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add(new SqlParameter("@TicketID", TicketID));
                    conn.Open();
                    Ticket obj = new Ticket();
                    using (IDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                    {
                        if (reader.Read())
                        {
                            obj = GetObjectFromReader(reader);
                        }
                    }
                    conn.Close();
                    return obj;
                }
            }

            ///<summary>
            ///Get List Ticket as DataTable
            ///</summary>
            public DataTable PrintTicket(int TicketID)
            {
                using (SqlConnection conn = CreateConnection())
                {
                    SqlCommand cmd = new SqlCommand(usp_PrintTicketInfo, conn);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add(new SqlParameter("@TicketID", TicketID));
                    SqlDataAdapter dataAdapter = new SqlDataAdapter(cmd);
                    try
                    {
                        conn.Open();
                        DataTable tbl = new DataTable();
                        dataAdapter.Fill(tbl);
                        conn.Close();
                        return tbl;
                    }
                    catch
                    {
                        return null;
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open)
                            conn.Close();
                    }
                }
            }

            public DataTable SearchTicketInfoByID(int TicketID)
            {
                using (SqlConnection conn = CreateConnection())
                {
                    SqlCommand cmd = new SqlCommand(usp_SearchTicketInfoByID, conn);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add(new SqlParameter("@TicketID", TicketID));
                    SqlDataAdapter dataAdapter = new SqlDataAdapter(cmd);
                    try
                    {
                        conn.Open();
                        DataTable tbl = new DataTable();
                        dataAdapter.Fill(tbl);
                        conn.Close();
                        return tbl;
                    }
                    catch
                    {
                        return null;
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open)
                            conn.Close();
                    }
                }
            }

            ///<summary>
            ///Get List Ticket as List
            ///</summary>
            /// <param name="whereSql">Where fields. example : 1=1 </param>
            /// <param name="orderBy"Order fields></param>
            public List<Ticket> GetList_Ticket(string whereSql, string orderBy)
            {
                using (SqlConnection conn = CreateConnection())
                {
                    SqlCommand cmd = new SqlCommand(usp_SelecttbTicketsDynamic, conn);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add(new SqlParameter("@WhereCondition", whereSql));
                    cmd.Parameters.Add(new SqlParameter("@OrderByExpression", orderBy));
                    conn.Open();
                    List<Ticket> lst = new List<Ticket>();
                    try
                    {
                        using (IDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (reader.Read())
                            {
                                lst.Add(GetObjectFromReader(reader));
                            }
                        }
                        conn.Close();
                        return lst;
                    }
                    catch
                    {
                        return null;
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open)
                            conn.Close();
                    }
                }
            }

            ///<summary>
            ///Get List Ticket as List
            ///</summary>
            /// <param name="whereSql">Where fields. example : 1=1 </param>
            /// <param name="orderBy"Order fields></param>
            public DataTable GetList_TicketInfo()
            {
                using (SqlConnection conn = CreateConnection())
                {
                    SqlCommand cmd = new SqlCommand(usp_SelectTicketInfo, conn);
                    cmd.CommandType = CommandType.StoredProcedure;
                    SqlDataAdapter dataAdapter = new SqlDataAdapter(cmd);
                    try
                    {
                        conn.Open();
                        DataTable tbl = new DataTable();
                        dataAdapter.Fill(tbl);
                        conn.Close();
                        return tbl;
                    }
                    catch
                    {
                        return null;
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open)
                            conn.Close();
                    }
                    
                }
            }

            public DataTable Search_TicketInfo(string CustName, DateTime date, int status)
            {
                using (SqlConnection conn = CreateConnection())
                {
                    SqlCommand cmd = new SqlCommand(usp_SearchTicketInfo, conn);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add(new SqlParameter("@PassengerName", CustName));
                    cmd.Parameters.Add(new SqlParameter("@Date", date));
                    cmd.Parameters.Add(new SqlParameter("@Status", status));
                    SqlDataAdapter dataAdapter = new SqlDataAdapter(cmd);
                    try
                    {
                        conn.Open();
                        DataTable tbl = new DataTable();
                        dataAdapter.Fill(tbl);
                        conn.Close();
                        return tbl;
                    }
                    catch
                    {
                        return null;
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open)
                            conn.Close();
                    }

                }
            }

            public DataTable Search_TicketByStatus(int status)
            {
                using (SqlConnection conn = CreateConnection())
                {
                    SqlCommand cmd = new SqlCommand(usp_SearchTicketByStatus, conn);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add(new SqlParameter("@Status", status));
                    SqlDataAdapter dataAdapter = new SqlDataAdapter(cmd);
                    try
                    {
                        conn.Open();
                        DataTable tbl = new DataTable();
                        dataAdapter.Fill(tbl);
                        conn.Close();
                        return tbl;
                    }
                    catch
                    {
                        return null;
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open)
                            conn.Close();
                    }

                }
            }

            ///<summary>
            ///Get List Ticket 
            ///</summary>
            /// <param name="whereSql">Where fields. example : 1=1 </param>
            /// <param name="orderBy"Order fields></param>
            public List<Ticket> GetAll_Ticket()
            {
                using (SqlConnection conn = CreateConnection())
                {
                    SqlCommand cmd = new SqlCommand(usp_SelecttbTicketsAll, conn);
                    cmd.CommandType = CommandType.StoredProcedure;
                    conn.Open();
                    List<Ticket> lst = new List<Ticket>();
                    using (IDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                    {
                        while (reader.Read())
                        {
                           lst.Add(GetObjectFromReader(reader));
                        }
                    }
                    conn.Close();
                    return lst;
                }
            }

            ///<summary>
            ///Using for paging data 
            ///</summary>
            /// <param name="pageIndex">Number of pages</param>
            /// <param name="PageSize">Number of a rows per page</param>
            /// <param name="conditions">Where  fields</param>
            /// <param name="groupBy">Group by fields</param>
            public List<Ticket> Get_TicketAsPaging(string sWhere, string sOrderBy, int pageIndex, int pageSize)
            {
                using (SqlConnection conn = CreateConnection())
                {
                    SqlCommand cmd = new SqlCommand(usp_SelecttbTicketsPaged, conn);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add(new SqlParameter("@Where", sWhere));
                    cmd.Parameters.Add(new SqlParameter("@OrderBy", sOrderBy));
                    cmd.Parameters.Add(new SqlParameter("@PageIndex", pageIndex));
                    cmd.Parameters.Add(new SqlParameter("@PageSize", pageSize));
                    conn.Open();
                    List<Ticket> lst = new List<Ticket>();
                    try
                    {
                        using (IDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (reader.Read())
                            {
                                lst.Add(GetObjectFromReader(reader));
                            }
                        }
                        conn.Close();
                        return lst;
                    }
                    catch
                    {
                        return null;
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open)
                            conn.Close();
                    }
                }
            }

            ///<summary>
            ///Insert new Ticket
            ///</summary>
            public bool Insert_Ticket(Ticket temp)
            {
                using (SqlConnection conn = CreateConnection())
                {
                    SqlCommand cmd = new SqlCommand(usp_InserttbTicket, conn);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add(new SqlParameter("@OrderID", temp.OrderID));
                    cmd.Parameters.Add(new SqlParameter("@FromStation", temp.FromStation));
                    cmd.Parameters.Add(new SqlParameter("@ToStation", temp.ToStation));
                    cmd.Parameters.Add(new SqlParameter("@FromStationNumber", temp.FromStationNumber));
                    cmd.Parameters.Add(new SqlParameter("@ToStationNumber", temp.ToStationNumber));
                    cmd.Parameters.Add(new SqlParameter("@ScheduleID", temp.ScheduleID));
                    cmd.Parameters.Add(new SqlParameter("@SeatNo", temp.SeatNo));
                    cmd.Parameters.Add(new SqlParameter("@CoachTypeID", temp.CoachTypeID));
                    cmd.Parameters.Add(new SqlParameter("@Fare", temp.Fare));
                    cmd.Parameters.Add(new SqlParameter("@Status", temp.Status));
                    cmd.Parameters.Add(new SqlParameter("@Date", temp.Date));
                    cmd.Parameters.Add(new SqlParameter("@ExpirationDate", temp.ExpirationDate));
                    try
                    {
                        conn.Open();
                        cmd.ExecuteNonQuery();
                        conn.Close();
                    }
                    catch (SqlException ex)
                    {
                        return false;
                        throw ex;
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open)
                            conn.Close();
                    }
                    return true;
                }
            }

            ///<summary>
            ///Update a Ticket
            ///</summary>
            public bool Update_Ticket(Ticket temp)
            {
                using (SqlConnection conn = CreateConnection())
                {
                    SqlCommand cmd = new SqlCommand(usp_UpdatetbTicket, conn);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add(new SqlParameter("@TicketID", temp.TicketID));
                    cmd.Parameters.Add(new SqlParameter("@OrderID", temp.OrderID));
                    cmd.Parameters.Add(new SqlParameter("@FromStation", temp.FromStation));
                    cmd.Parameters.Add(new SqlParameter("@ToStation", temp.ToStation));
                    cmd.Parameters.Add(new SqlParameter("@FromStationNumber", temp.FromStationNumber));
                    cmd.Parameters.Add(new SqlParameter("@ToStationNumber", temp.ToStationNumber));
                    cmd.Parameters.Add(new SqlParameter("@ScheduleID", temp.ScheduleID));
                    cmd.Parameters.Add(new SqlParameter("@SeatNo", temp.SeatNo));
                    cmd.Parameters.Add(new SqlParameter("@CoachTypeID", temp.CoachTypeID));
                    cmd.Parameters.Add(new SqlParameter("@Fare", temp.Fare));
                    cmd.Parameters.Add(new SqlParameter("@Status", temp.Status));
                    cmd.Parameters.Add(new SqlParameter("@Date", temp.Date));
                    cmd.Parameters.Add(new SqlParameter("@ExpirationDate", temp.ExpirationDate));
                    
                    try
                    {
                        conn.Open();
                        cmd.ExecuteNonQuery();
                        conn.Close();
                    }
                    catch (SqlException ex)
                    {
                        return false;
                        throw ex;
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open)
                            conn.Close();
                    }
                    return true;
                }
            }

            ///<summary>
            ///Update a Ticket
            ///</summary>
            public bool Update_Ticket_Status(Ticket temp)
            {
                using (SqlConnection conn = CreateConnection())
                {
                    SqlCommand cmd = new SqlCommand(usp_UpdateTicketStatus, conn);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add(new SqlParameter("@TicketID", temp.TicketID));
                    cmd.Parameters.Add(new SqlParameter("@Status", temp.Status));

                    try
                    {
                        conn.Open();
                        cmd.ExecuteNonQuery();
                        conn.Close();
                    }
                    catch (SqlException ex)
                    {
                        return false;
                        throw ex;
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open)
                            conn.Close();
                    }
                    return true;
                }
            }


            ///<summary>
            ///Delete a Ticket
            ///</summary>
            public bool Delete_Ticket(int id)
            {
                using (SqlConnection conn = CreateConnection())
                {
                    SqlCommand cmd = new SqlCommand(usp_DeletetbTicket, conn);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add(new SqlParameter("@TicketID", id));
                    try
                    {
                        conn.Open();
                        cmd.ExecuteNonQuery();
                        conn.Close();
                    }
                    catch (SqlException ex)
                    {
                        return false;
                        throw ex;
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open)
                            conn.Close();
                    }
                    return true;
                }
            }

            #endregion
        }
}
